package bma.common.jdbctemplate;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import bma.common.langutil.core.Function;
import bma.common.langutil.core.ValueUtil;
import bma.common.langutil.jdbc.FieldValues;
import bma.common.langutil.jdbc.JdbcTypeMapper;
import bma.common.langutil.jdbc.StatementHelper;

public class JdbcTemplateHelper {

	private JdbcTemplate jdbcTemplate;
	private StatementHelper statementHelper = StatementHelper.JDBC;
	private Function<Object, Integer> typeMapper = JdbcTypeMapper.O;

	public JdbcTemplateHelper() {
		super();
	}

	public JdbcTemplateHelper(JdbcTemplate jdbcTemplate) {
		super();
		this.jdbcTemplate = jdbcTemplate;
	}

	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	public StatementHelper getStatementHelper() {
		return statementHelper;
	}

	public void setStatementHelper(StatementHelper statementHelper) {
		this.statementHelper = statementHelper;
	}

	public Function<Object, Integer> getTypeMapper() {
		return typeMapper;
	}

	public void setTypeMapper(Function<Object, Integer> typeMapper) {
		this.typeMapper = typeMapper;
	}

	public Number executeInsert(final String tableName, final FieldValues fvs,
			final String genPkId) {
		return executeInsert(tableName, fvs, genPkId, null);
	}

	public Number executeInsert(final String tableName, final FieldValues fvs,
			final String genPkId, final SQLFilter sqlf) {

		PreparedStatementCreator psc = new PreparedStatementCreator() {

			@Override
			public PreparedStatement createPreparedStatement(Connection con)
					throws SQLException {
				String sql = statementHelper.buildInsert(tableName, fvs);
				if (sqlf != null) {
					sql = sqlf.filter(sql);
				}
				int idx = 1;
				PreparedStatement ps = null;
				if (ValueUtil.notEmpty(genPkId)) {
					ps = con.prepareStatement(sql.toString(),
							new String[] { genPkId });
				} else {
					ps = con.prepareStatement(sql.toString());
				}
				statementHelper.buildParam(fvs, ps, idx, typeMapper);
				return ps;
			}
		};

		if (ValueUtil.notEmpty(genPkId)) {
			KeyHolder keyHolder = new GeneratedKeyHolder();
			jdbcTemplate.update(psc, keyHolder);
			return keyHolder.getKey();
		} else {
			int c = jdbcTemplate.update(psc);
			return c;
		}
	}

	public int executeUpdate(final String tableName, final FieldValues fvs,
			final FieldValues tj) {
		return jdbcTemplate.update(new PreparedStatementCreator() {

			@Override
			public PreparedStatement createPreparedStatement(Connection con)
					throws SQLException {
				String sql = statementHelper.buildUpdate(tableName, fvs, tj);

				int idx = 1;
				PreparedStatement ps = con.prepareStatement(sql);
				idx = statementHelper.buildParam(fvs, ps, idx, typeMapper);
				idx = statementHelper.buildParam(tj, ps, idx, typeMapper);
				return ps;
			}
		});
	}

	public int executeDelete(final String tableName, final FieldValues tj) {
		PreparedStatementCreator psc = new PreparedStatementCreator() {

			@Override
			public PreparedStatement createPreparedStatement(Connection con)
					throws SQLException {
				String sql = statementHelper.buildDelete(tableName, tj);
				int idx = 1;
				PreparedStatement ps = con.prepareStatement(sql.toString());
				if (tj.notEmpty()) {
					statementHelper.buildParam(tj, ps, idx, typeMapper);
				}
				return ps;
			}
		};

		return jdbcTemplate.update(psc);
	}

	public int selectCount(String tableName, final FieldValues tj) {
		StringBuffer buf = new StringBuffer(256);
		buf.append("SELECT COUNT(*) FROM ").append(tableName);
		if (tj.notEmpty()) {
			buf.append(" WHERE ");
			statementHelper.buildCriteria(buf, tj, null, false);
		}

		return jdbcTemplate.query(buf.toString(),
				new PreparedStatementSetter() {

					@Override
					public void setValues(PreparedStatement ps)
							throws SQLException {
						int idx = 1;
						StatementHelper.JDBC
								.buildParam(tj, ps, idx, typeMapper);
					}
				}, new ResultSetExtractor<Integer>() {
					@Override
					public Integer extractData(ResultSet rs)
							throws SQLException, DataAccessException {
						rs.next();
						return rs.getInt(1);
					}
				});
	}

	public <TYPE> List<TYPE> selectLimit(String tableName,
			final FieldValues tj, String orderStr, final int start,
			final int pageSize, RowMapper<TYPE> reader) {
		StringBuffer buf = new StringBuffer(256);
		buf.append("SELECT * FROM ").append(tableName);
		if (tj.notEmpty()) {
			buf.append(" WHERE ");
			statementHelper.buildCriteria(buf, tj, null, false);
		}
		if (ValueUtil.notEmpty(orderStr)) {
			buf.append(" ORDER BY ").append(orderStr);
		}
		buf.append(" LIMIT ?,?");

		return jdbcTemplate.query(buf.toString(),
				new PreparedStatementSetter() {

					@Override
					public void setValues(PreparedStatement ps)
							throws SQLException {
						int idx = 1;
						idx = statementHelper.buildParam(tj, ps, idx,
								typeMapper);
						ps.setInt(idx++, start);
						ps.setInt(idx++, pageSize);
					}
				}, reader);
	}

	public <TYPE> TYPE selectOne(String tableName, String field, Object pk,
			RowMapper<TYPE> reader) {
		List<TYPE> list = jdbcTemplate.query("SELECT * FROM " + tableName
				+ " WHERE " + field + " = ?", new Object[] { pk }, reader);
		return list.isEmpty() ? null : list.get(0);
	}
}
